using System;
using System.Collections.Generic;
using System.Text;

namespace MyLib.Data {
	/// <summary>
	/// AuthorityProvider
	/// </summary>
	[System.ComponentModel.DataObject]
	public class AuthorityProvider : MyLib.DbContextProvider {
		/// <summary>
		/// Default default password.
		/// </summary>
		private const string DefaultDefaultPassword = "88888888";

		/// <summary>
		/// Default hash salt.
		/// </summary>
		private const string DefaultHashPasswordHashSalt = "345";

		/// <summary>
		/// Default hash alg.
		/// </summary>
		private const string DefaultHashAlg = "MD5";

		private string m_defaultPassword = DefaultDefaultPassword;

		/// <summary>
		/// Default password.
		/// </summary>
		public string DefaultPassword {
			get {
				return m_defaultPassword;
			}
			protected set {
				m_defaultPassword = value;
			}
		}

		/// <summary>
		/// Validate
		/// </summary>
		/// <param name="userName"></param>
		/// <param name="password"></param>
		/// <returns></returns>
		public UserTable Validate(
			string userName,
			string password) {
			var parameterUserName = new MyLib.DbParameter("user_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userName);
			var parameterPassword = new MyLib.DbParameter("password", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, HashPassword(password));

			// Create sql.

			var sql = @"
select 
  sec_user.user_id as userId, 
  sec_user.`name` as userName, 
  sec_user.real_name as realName, 
  sec_user.seq,
  sec_user.grp_id as grpId,
  sec_user.`status`, 
  group_concat(sec_user_role.role_id) roles
from sec_user left join sec_user_role on sec_user_role.user_id = sec_user.user_id 
inner join sec_pm on sec_pm.user_id = sec_user.user_id
where sec_user.`name` = #user_name#
and sec_pm.password = #password#
and sec_user.`status` = 1
group by sec_user.`user_id`";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = new UserTable();
				session.ExecuteSingleQuery(c, sql, System.Data.CommandType.Text, parameterUserName, parameterPassword);

				return c;
			}
		} // end of Validate


		/// <summary>
		/// Find users.
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userName"></param>
		/// <param name="realName"></param>
		/// <param name="status"></param>
		/// <param name="groupId"></param>
		/// <param name="roles"></param>
		/// <param name="maximumRows"></param>
		/// <param name="startRowIndex"></param>
		/// <returns></returns>
		[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
		public System.Data.DataTable FindUsers(
			int applicationId = 0,
			string userName = "",
			string realName = "",
			int? status = null,
			string groupId = "",
			string roles = "",
			int? maximumRows = null,
			int? startRowIndex = null) {

			if (userName != null) {
				userName = userName.Trim();
			}
			if (realName != null) {
				realName = realName.Trim();
			}
			if (groupId != null) {
				groupId = groupId.Trim();
			}
			if (roles != null) {
				roles = roles.Trim();
			}

			var parameterUserName = new MyLib.DbParameter("user_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, userName);
			var parameterRealName = new MyLib.DbParameter("real_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, realName);
			var parameterStatus = new MyLib.DbParameter("status", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, status);
			var parameterGroupId = new MyLib.DbParameter("grp_id", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, groupId);
			var parameterRoles = new MyLib.DbParameter("roles", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, roles);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"
select 
  sec_user.`user_id` as userId, 
  sec_user.`name` as userName, 
  sec_user.`real_name` as realName, 
  sec_user.`seq`,
  sec_user.`grp_id` as grpId,
  sec_user.`status`, 
  coalesce(group_concat(sec_user_role.`role_id`), '') roles,
  coalesce(group_concat(sec_role.`display_name`), '') roleNames
from sec_user 
left join sec_user_role on sec_user_role.`user_id` = sec_user.`user_id`
  and sec_user_role.application_id = #application_id#
left join sec_role on sec_role.`role_id` = sec_user_role.`role_id`
  and sec_role.application_id = #application_id#");

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(userName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.`name` like concat('%', #user_name#, '%')");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(realName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.`real_name` like concat('%', #real_name#, '%')");

				dynamicFlag1 = true;
			}
			if (status.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.status = #status#");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(groupId)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.grp_id = #grp_id#");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(roles)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"exists(select sec_user_role.`user_id` from sec_user_role where sec_user_role.`user_id` = sec_user.`user_id` and sec_user_role.`role_id` in ('$roles$'))");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" where ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			sqlBuilder.AppendLine(@"group by `userId`
			order by sec_user.seq, convert(sec_user.`name` using gbk)");

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = session.ExecutePagedQuery(sql, System.Data.CommandType.Text, startRowIndex ?? 0, maximumRows ?? int.MaxValue,
					parameterUserName,
					parameterRealName,
					parameterStatus,
					parameterGroupId,
					parameterRoles,
					parameterApplicationId);

				return c;
			}
		} // end of FindUsers

		/// <summary>
		/// Find count of users.
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userName"></param>
		/// <param name="realName"></param>
		/// <param name="status"></param>
		/// <param name="groupId"></param>
		/// <param name="roles"></param>
		/// <returns></returns>
		public int FindUsersCount(
			int applicationId = 0,
			string userName = "",
			string realName = "",
			int? status = null,
			string groupId = "",
			string roles = "") {

			if (userName != null) {
				userName = userName.Trim();
			}
			if (realName != null) {
				realName = realName.Trim();
			}
			if (groupId != null) {
				groupId = groupId.Trim();
			}
			if (roles != null) {
				roles = roles.Trim();
			}

			var parameterUserName = new MyLib.DbParameter("user_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, userName);
			var parameterRealName = new MyLib.DbParameter("real_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, realName);
			var parameterStatus = new MyLib.DbParameter("status", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, status);
			var parameterGroupId = new MyLib.DbParameter("grp_id", DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, groupId);
			var parameterRoles = new MyLib.DbParameter("roles", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, roles);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@" 
select 
  count(sec_user.`user_id`)
from sec_user");

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(userName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.`name` like concat('%', #user_name#, '%')");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(realName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.`real_name` like concat('%', #real_name#, '%')");

				dynamicFlag1 = true;
			}
			if (status.HasValue) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.status = #status#");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(groupId)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sec_user.grp_id = #grp_id#");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(roles)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"exists(select sec_user_role.`user_id` from sec_user_role where sec_user_role.`user_id` = sec_user.`user_id` and sec_user_role.`role_id` in ('$roles$'))");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" where ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = session.ExecuteScalar<int>(sql, System.Data.CommandType.Text,
					parameterUserName,
					parameterRealName,
					parameterStatus,
					parameterGroupId,
					parameterRoles);

				return c;
			}
		} // end of FindUsersCount

		/// <summary>
		/// FindUserById
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userId"></param>
		/// <returns></returns>
		public UserTable FindUserById(
			int applicationId,
			string userId) {

			if (string.IsNullOrEmpty(userId)) {
				return null;
			} else if (userId == "0") {
				var t = new UserTable();

				t.AddNewRow();
				t.AcceptChanges();

				return t;
			}

			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, userId);
			var parameterApplicationId = new MyLib.DbParameter("application_id", DbParameterType.Integer, applicationId);
			// Create sql.

			var sql = @"
select 
  sec_user.user_id `userId`, 
  sec_user.`name` userName, 
  sec_user.real_name realName, 
  sec_user.seq,
  sec_user.grp_id grpId,
  sec_user.`status`, 
  coalesce(group_concat(sec_user_role.role_id), '') roles,
  coalesce(group_concat(sec_role.display_name), '') roleNames
from sec_user 
left join sec_user_role on `sec_user_role`.`user_id` = `sec_user`.`user_id` and `sec_user_role`.`application_id` = #application_id#
left join sec_role on `sec_role`.`role_id` = `sec_user_role`.`role_id` and `sec_role`.`application_id` = #application_id#
where sec_user.user_id = #user_id#
group by sec_user.`user_id`";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = new UserTable();
				session.ExecuteSingleQuery(c, sql, System.Data.CommandType.Text,
					parameterUserId,
					parameterApplicationId);

				return c;
			}
		} // end of FindUserById

		/// <summary>
		/// FindUserByName
		/// </summary>
		/// <param name="userName" ></param>
		/// <returns></returns>
		public UserTable FindUserByName(
			string userName) {
			var parameterUserName = new MyLib.DbParameter("user_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userName);

			// Create sql.

			var sql = @"
select 
  sec_user.user_id as `userId`, 
  sec_user.`name` as userName, 
  sec_user.real_name as realName, 
  sec_user.seq,
  sec_user.grp_id as grpId,
  sec_user.`status`, 
  group_concat(sec_user_role.role_id) as roles
from sec_user left join sec_user_role on sec_user_role.user_id = sec_user.user_id 
where sec_user.`name` = #user_name#
group by sec_user.`user_id`";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = new UserTable();
				session.ExecuteSingleQuery(c, sql, System.Data.CommandType.Text, parameterUserName);

				return c;
			}
		} // end of FindUserById

		/// <summary>
		/// 
		/// </summary>
		/// <param name="userId"></param>
		/// <returns></returns>
		public UserExtTable FindUserExtByUserId(string userId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);

			// Create sql.

			var sql = @"
select
  sec_user_ext.ext_id as extId,
  sec_user_ext.ext_value as extValue
from sec_user_ext
where sec_user_ext.user_id = #user_id#
order by sec_user_ext.ext_id";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = new UserExtTable();
				session.ExecuteQuery(c, sql, System.Data.CommandType.Text, parameterUserId);

				return c;
			}
		}

		/// <summary>
		/// Insert or update user.
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userId"></param>
		/// <param name="userName"></param>
		/// <param name="realName"></param>
		/// <param name="grpId"></param>
		/// <param name="status"></param>
		/// <param name="roles"></param>
		/// <returns></returns>
		public int SaveUser(
			int applicationId,
			ref string userId,
			string userName,
			string realName,
			string grpId,
			int status,
			string roles = null) {
			if (userId == null) {
				userId = string.Empty;
			}

			var parameterApplicationId = new MyLib.DbParameter("application_id", DbParameterType.String, applicationId);
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, userId);
			var parameterUserName = new MyLib.DbParameter("user_name", MyLib.DbParameterType.String, userName);
			var parameterRealName = new MyLib.DbParameter("real_name", MyLib.DbParameterType.String, realName);
			var parameterPassword = new MyLib.DbParameter("password", MyLib.DbParameterType.String, HashPassword(DefaultPassword));
			var parameterStatus = new MyLib.DbParameter("status", MyLib.DbParameterType.Integer, status);
			var parameterGroupId = new MyLib.DbParameter("grp_id", DbParameterType.String, grpId);
			var parameterRoleId = new MyLib.DbParameter("role_id", MyLib.DbParameterType.String, null);

			// Create sql.
			var sql1 = @"select exists(
			select sec_user.user_id from sec_user
			where sec_user.name = #user_name#
			and sec_user.user_id <> #user_id#)";

			var sql2 = @"update sec_user
set `name` = #user_name#, real_name = #real_name#, grp_id = #grp_id#,
`status` = #status#
where `user_id` = #user_id#";

			var sql3 = @"insert into sec_user
			(user_id, `name`, real_name, seq, grp_id, `status`)
			select #user_id#, #user_name#, #real_name#, ifnull(max(seq), 0) + 1, #grp_id#, #status#
			from sec_user";

			var sql4 = @"insert into sec_pm
			(user_id, password)
			values(#user_id#, #password#)";

			var sql5 = @"delete from sec_user_role where `user_id` = #user_id# and `application_id` = #application_id#";

			var sql6 = @"insert sec_user_role (`application_id`, `user_id`, `role_id`) values(#application_id#, #user_id#, #role_id#)";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {
				var c = 0;

				var ef = session.ExecuteScalar<bool>(
					sql1, System.Data.CommandType.Text,
					parameterUserId, parameterUserName);

				if (ef) {
					throw new ApplicationException(Properties.Main.ErrorDuplicatedUserName);
				}

				// Do query.
				if (userId.Length == 0) {
					userId = Convert.ToString(IdGenerator.NewId("sec_user"));
					parameterUserId.Value = userId;

					// Insert user.
					c = session.ExecuteUpdate(
						sql3, System.Data.CommandType.Text,
						parameterUserId, parameterUserName, parameterRealName,
						parameterGroupId, parameterStatus);

					// Insert password.
					session.ExecuteUpdate(
						sql4, System.Data.CommandType.Text,
						parameterUserId, parameterPassword);
				} else {
					// Update user.
					c = session.ExecuteUpdate(
						sql2, System.Data.CommandType.Text,
						parameterUserId, parameterUserName, parameterRealName,
						parameterGroupId, parameterStatus);

					// Remove all roles.
					session.ExecuteUpdate(
						sql5, System.Data.CommandType.Text,
						parameterApplicationId,
						parameterUserId);
				}

				// Insert roles.
				if (!string.IsNullOrEmpty(roles)) {
					foreach (var role in roles.Split(',')) {
						parameterRoleId.Value = role.Trim();

						session.ExecuteUpdate(sql6, System.Data.CommandType.Text,
							parameterApplicationId,
							parameterUserId, parameterRoleId);
					}
				}

				session.Commit();

				return c;
			}
		} // end of SaveUser

		/// <summary>
		/// Disable user.
		/// </summary>
		/// <param name="userId"></param>
		/// <returns></returns>
		public int DisableUser(
			string userId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);

			// Create sql.

			var sql = @"update sec_user set `status` = 0 where user_id = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {

				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId);

				session.Commit();


				return c;
			}
		} // end of DisableUser

		/// <summary>
		/// EnableUser
		/// </summary>
		/// <param name="userId" ></param>
		/// <returns></returns>
		public int EnableUser(
			string userId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);

			// Create sql.

			var sql = @"update sec_user set `status` = 1 where user_id = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {

				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId);

				session.Commit();


				return c;
			}
		} // end of EnableUser

		/// <summary>
		/// 
		/// </summary>
		/// <param name="originValue">Plain password</param>
		/// <param name="salt">Salt</param>
		/// <param name="saltP">Position of salt</param>
		/// <param name="hashIteration">Iteration of hash</param>
		/// <returns></returns>
		private static string HashPassword(string originValue, string salt = DefaultHashPasswordHashSalt, int saltP = 2, int hashIteration = 2) {
			// Check empty.
			if (string.IsNullOrEmpty(originValue))
				return string.Empty;

			// Password is ignorecase.
			originValue = originValue.ToLower();

			// Add salt.
			if (originValue.Length < saltP)
				originValue = originValue + salt;
			else
				originValue = originValue.Substring(0, saltP) + salt + originValue.Substring(saltP);

			var alg = System.Security.Cryptography.HashAlgorithm.Create(DefaultHashAlg);
			using (alg) {
				var bytes = System.Text.Encoding.UTF8.GetBytes(originValue);

				for (int i = 0; i < hashIteration; ++i) {
					bytes = alg.ComputeHash(bytes);
				}

				var sb = new System.Text.StringBuilder();
				foreach (var b in bytes) {
					sb.AppendFormat("{0:X2}", b);
				}

				return sb.ToString();

			}
		} // end of HashPassword.


		/// <summary>
		/// 
		/// </summary>
		/// <param name="userId"></param>
		/// <returns></returns>
		[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
		public ChangePasswordTable RetrievePassword(string userId) {
			var dt = new ChangePasswordTable();
			var row = dt.AddNewRow();
			row.UserId = userId;

			dt.AcceptChanges();

			return dt;
		}

		/// <summary>
		/// Reset password.
		/// </summary>
		/// <param name="userId"></param>
		public void ResetPassword(string userId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);
			var parameterNewPassword = new MyLib.DbParameter("new_password", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, HashPassword(DefaultPassword));

			// Create sql.

			var sql2 = @"update sec_pm join sec_user su on su.user_id = sec_pm.user_id
			set `password` = #new_password#
			where su.`user_id` = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {
				// Do query.

				var c = session.ExecuteUpdate(sql2, System.Data.CommandType.Text,
					parameterUserId, parameterNewPassword);

				session.Commit();
			}
		}


		/// <summary>
		/// ChangePassword
		/// </summary>
		/// <param name="userId" ></param>
		/// <param name="password"></param>
		/// <param name="newPassword" ></param>
		/// <returns></returns>
		[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Update)]
		public int ChangePassword(
			string userId,
			string password,
			string newPassword) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);
			var parameterPassword = new MyLib.DbParameter("password", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, HashPassword(password));
			var parameterNewPassword = new MyLib.DbParameter("new_password", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, HashPassword(newPassword));

			// Create sql.

			var sql1 = @"select exists(
			select sec_pm.user_id from sec_pm
			where sec_pm.user_id = #user_id#
			and sec_pm.password = #password#)";

			var sql2 = @"update sec_pm join sec_user su on su.user_id = sec_pm.user_id
			set `password` = #new_password#
			where su.`user_id` = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {
				var v = session.ExecuteScalar<bool>(sql1, System.Data.CommandType.Text, parameterUserId, parameterPassword);
				if (!v) {
					throw new ApplicationException(Properties.Main.ErrorOldPassword);
				}

				// Do query.

				var c = session.ExecuteUpdate(sql2, System.Data.CommandType.Text,
					parameterUserId, parameterNewPassword);

				session.Commit();


				return c;
			}
		} // end of ChangePassword

		/// <summary>
		/// DeleteUser
		/// </summary>
		/// <param name="userId" ></param>
		/// <returns></returns>
		public int DeleteUser(
			string userId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);

			// Create sql.

			var sql = @"delete from sec_user where user_id = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {

				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId);

				session.Commit();


				return c;
			}
		} // end of RemoveUser

		/// <summary>
		/// DeleteUserRole
		/// </summary>
		/// <param name="userId" ></param>
		/// <returns></returns>
		public int DeleteUserRole(
			string userId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);

			// Create sql.

			var sql = @"delete from sec_user_role where user_id = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {

				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId);

				session.Commit();


				return c;
			}
		} // end of DeleteUserRole

		/// <summary>
		/// CreateUserRole
		/// </summary>
		/// <param name="userId" ></param>
		/// <param name="roleId" ></param>
		/// <returns></returns>
		public int CreateUserRole(
			string userId,
			string roleId) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);
			var parameterRoleId = new MyLib.DbParameter("role_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, roleId);

			// Create sql.

			var sql = @"insert into sec_user_role
			(user_id, role_id)
			values(#user_id#, #role_id#)";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {

				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId, parameterRoleId);

				session.Commit();


				return c;
			}
		} // end of CreateUserRole

		/// <summary>
		/// DeleteUserExt
		/// </summary>
		/// <param name="userId" ></param>
		/// <param name="extIdArray"></param>
		/// <returns></returns>
		public int DeleteUserExt(
			string userId,
			params int[] extIdArray) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);
			var parameterExtIdArray = new MyLib.DbParameter("ext_id_array", DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, extIdArray);
			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"delete from sec_user_ext where user_id = #user_id#");

			// Sql text.

			var sql = sqlBuilder.ToString();

			if (extIdArray != null && extIdArray.Length != 0) {
				sqlBuilder.AppendLine("and ext_id in ($ext_id_array$)");
			}

			using (var session = SessionFactory.CreateSession()) {
				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId, parameterExtIdArray);

				session.Commit();

				return c;
			}
		} // end of DeleteUserExt

		/// <summary>
		/// CreateUserExt
		/// </summary>
		/// <param name="userId" ></param>
		/// <param name="extId"></param>
		/// <param name="extValue"></param>
		/// <returns></returns>
		public int CreateUserExt(
			string userId,
			int extId,
			string extValue) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, userId);
			var parameterExtId = new MyLib.DbParameter("ext_id", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, extId);
			var parameterExtValue = new MyLib.DbParameter("ext_value", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, extValue);

			// Create sql.

			var sql = @"insert into sec_user_ext
			(user_id, ext_id, ext_value)
			values(#user_id#, #ext_id#, #ext_value#)";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {

				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text, parameterUserId, parameterExtId, parameterExtValue);

				session.Commit();


				return c;
			}
		} // end of CreateUserDep

		/// <summary>
		/// FindActionsByUserId
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userId" ></param>
		/// <returns></returns>
		public IList<int> FindActionsByUserId(
			int applicationId,
			string userId) {

			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, userId);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sql = @"select distinct sra.action_id
from sec_user as su
join sec_user_role as sur on su.user_id = sur.user_id and sur.application_id = #application_id#
join sec_role as sr on sur.role_id = sr.role_id and sur.application_id = sr.application_id
join sec_role_action as sra on sr.role_id = sra.role_id and sr.application_id = sra.application_id
where su.user_id = #user_id#";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = session.ExecuteList<int>(sql, System.Data.CommandType.Text,
					parameterUserId,
					parameterApplicationId);

				return c;
			}
		} // end of FindActionsByUserId

		/// <summary>
		/// IsAuthorized
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userId" ></param>
		/// <param name="actionId" ></param>
		/// <returns></returns>
		public bool IsAuthorized(
			int applicationId,
			string userId,
			int actionId) {

			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, userId);
			var parameterActionId = new MyLib.DbParameter("action_id", MyLib.DbParameterType.Integer, actionId);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sql = @"select exists
(select su.user_id 
from sec_user as su
join sec_user_role as sur on su.user_id = sur.user_id and sur.application_id = #application_id#
join sec_role as sr on sur.role_id = sr.role_id and sur.application_id = sr.application_id
join sec_role_action as sra on sr.role_id = sra.role_id and sr.application_id = sra.application_id
where su.user_id = #user_id#
and sra.action_id = #action_id#)";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = session.ExecuteScalar<bool>(sql, System.Data.CommandType.Text,
					parameterUserId,
					parameterActionId,
					parameterApplicationId);

				return c;
			}
		} // end of IsAuthorized

		/// <summary>
		/// IsAuthorized
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="userId" ></param>
		/// <param name="actionIdArray" ></param>
		/// <returns></returns>
		public bool IsAuthorized(
			int applicationId,
			string userId,
			int[] actionIdArray) {
			var parameterUserId = new MyLib.DbParameter("user_id", MyLib.DbParameterType.String, userId);
			var parameterActionIdArray = new MyLib.DbParameter("action_id", MyLib.DbParameterType.Integer, string.Join(",", actionIdArray));
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sql = @"select exists
(select su.user_id 
from sec_user as su
join sec_user_role as sur on su.user_id = sur.user_id and sur.application_id = #application_id#
join sec_role as sr on sur.role_id = sr.role_id and sur.application_id = sr.application_id
join sec_role_action as sra on sr.role_id = sra.role_id and sr.application_id = sra.application_id
where su.user_id = #user_id#
and sra.action_id in ($action_id$))";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {

				// Do query.

				var c = session.ExecuteScalar<bool>(sql, System.Data.CommandType.Text,
					parameterUserId,
					parameterActionIdArray,
					parameterApplicationId);

				return c;
			}
		} // end of IsAuthorized

		/// <summary>
		/// FindRoleById
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="roleId" ></param>
		/// <returns></returns>
		public RoleTable FindRoleById(
			int applicationId,
			string roleId) {
			if (string.IsNullOrEmpty(roleId)) {
				return null;
			} else if (roleId == "0") {
				var t = new RoleTable();

				t.AddNewRow();
				t.AcceptChanges();

				return t;
			}

			var parameterRoleId = new MyLib.DbParameter("role_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, roleId);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sql = @"select  
sr.role_id as roleId,
sr.seq as seq,
group_concat(sra.action_id) as actions,
display_name as displayName,
description
from sec_role  as sr
left join sec_role_action as sra using (`application_id`, `role_id`)
where sr.`role_id` = #role_id#
  and sr.`application_id` = #application_id#
group by sr.`role_id`
order by sr.seq";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {
				// Do query.

				var c = new RoleTable();
				session.ExecuteSingleQuery(c, sql, System.Data.CommandType.Text, parameterRoleId, parameterApplicationId);

				return c;
			}
		} // end of FindRoleById

		/// <summary>
		/// FindRoleByName
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="displayName" ></param>
		/// <returns></returns>
		public RoleTable FindRoleByName(
			int applicationId,
			string displayName) {

			System.Diagnostics.Debug.Assert(!string.IsNullOrEmpty(displayName));

			var parameterDisplayName = new MyLib.DbParameter("display_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, displayName);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sql = @"select  
sr.role_id as roleId,
sr.seq as seq,
group_concat(sra.action_id) as actions,
display_name as displayName,
description
from sec_role  as sr
left join sec_role_action as sra on sr.role_id = sra.role_id 
where sr.display_name = #display_name#
  and sr.`application_id` = #application_id#
group by sr.`role_id`
order by sr.seq";

			// Sql text.

			using (var session = SessionFactory.CreateReadOnlySession()) {
				// Do query.

				var c = new RoleTable();
				session.ExecuteSingleQuery(c, sql, System.Data.CommandType.Text,
					parameterDisplayName,
					parameterApplicationId);

				return c;
			}
		} // end of FindRoleById

		/// <summary>
		/// DeleteRole
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="roleId" ></param>
		/// <returns></returns>
		public int DeleteRole(
			int applicationId,
			string roleId) {

			System.Diagnostics.Debug.Assert(!string.IsNullOrWhiteSpace(roleId));

			var parameterId = new MyLib.DbParameter("role_id", MyLib.DbParameterType.String, roleId);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sql = @"delete from sec_role 
where role_id = #role_id# and application_id = #application_id#";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {
				// Do query.

				var c = session.ExecuteUpdate(sql, System.Data.CommandType.Text,
					parameterId,
					parameterApplicationId);

				session.Commit();

				return c;
			}
		} // end of DeleteRole

		/// <summary>
		/// UpdateRole
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="roleId" ></param>
		/// <param name="displayName" ></param>
		/// <param name="description" ></param>
		/// <param name="actions"></param>
		/// <returns></returns>
		public int SaveRole(
			int applicationId,
			ref string roleId,
			string displayName,
			string description,
			string actions = null) {

			var parameterRoleId = new MyLib.DbParameter("role_id", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, roleId);
			var parameterDisplayName = new MyLib.DbParameter("display_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, displayName);
			var parameterDescription = new MyLib.DbParameter("description", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, description);
			var parameterActionId = new MyLib.DbParameter("action_id", MyLib.DbParameterType.Integer, MyLib.DbParameterMatchType.Exact, 0, System.Data.ParameterDirection.Input, null);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.
			var sql1 = @"select exists(
select sec_role.role_id roleId from sec_role
where sec_role.display_name = #display_name#
and sec_role.role_id <> #role_id#
and sec_role.application_id = #application_id#)";

			var sql2 = @"update sec_role
set display_name = #display_name#,
description = #description#
where role_id = #role_id#
and application_id = #application_id#";

			var sql3 = @"insert into sec_role(application_id, role_id, seq, display_name, description)
select #application_id#, #role_id#, ifnull(max(seq), 0) + 1,	#display_name#,	#description# 
from sec_role
where application_id = #application_id#";

			var sql4 = @"delete from sec_role_action where role_id = #role_id# and application_id = #application_id#";

			var sql5 = @"insert into sec_role_action
(application_id, role_id, action_id)
values(#application_id#, #role_id#, #action_id#)";

			// Sql text.

			using (var session = SessionFactory.CreateSession()) {
				var c = 0;

				var ef = session.ExecuteScalar<bool>(
					sql1, System.Data.CommandType.Text,
					parameterRoleId,
					parameterDisplayName,
					parameterApplicationId);

				if (ef) {
					throw new ApplicationException(Properties.Main.ErrorDuplicatedRoleName);
				}

				// Do query.
				if (string.IsNullOrEmpty(roleId)) {
					roleId = Convert.ToString(IdGenerator.NewId("sec_role"));
					parameterRoleId.Value = roleId;

					// Insert role.
					c = session.ExecuteUpdate(sql3, System.Data.CommandType.Text,
						parameterRoleId, parameterDisplayName, parameterDescription, parameterApplicationId);
				} else {
					// Update roled.
					c = session.ExecuteUpdate(sql2, System.Data.CommandType.Text,
						parameterRoleId, parameterDisplayName, parameterDescription, parameterApplicationId);

					// Remove all actions.
					session.ExecuteUpdate(sql4, System.Data.CommandType.Text, parameterRoleId, parameterApplicationId);
				}

				// Insert actions.
				if (!string.IsNullOrEmpty(actions)) {
					foreach (var action in actions.Split(',')) {
						parameterActionId.Value = int.Parse(action);

						session.ExecuteUpdate(sql5, System.Data.CommandType.Text,
							parameterRoleId, parameterActionId, parameterApplicationId);
					}
				}

				session.Commit();

				return c;
			}
		} // end of UpdateRole

		/// <summary>
		/// FindRoles
		/// </summary>
		/// <param name="applicationId"></param>
		/// <param name="roleName" ></param>
		/// <param name="description" ></param>
		/// <param name="maximumRows"></param>
		/// <param name="startRowIndex"></param>
		/// <returns></returns>
		[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
		public RoleTable FindRoles(
			int applicationId,
			string roleName = "",
			string description = "",
			int? maximumRows = null,
			int? startRowIndex = null) {

			if (roleName != null) {
				roleName = roleName.Trim();
			}
			if (description != null) {
				description = description.Trim();
			}

			var parameterRoleName = new MyLib.DbParameter("role_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, roleName);
			var parameterDescription = new MyLib.DbParameter("description", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, description);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"select
sr.role_id as roleId,
sr.seq,
sr.display_name as displayName,
sr.description as description,
group_concat(sra.action_id) as actions
from sec_role as sr
left join sec_role_action as sra on sr.role_id = sra.role_id
where sr.application_id = #application_id#");

			// Create dynamic sql 1.
			var dynamicText1 = new System.Text.StringBuilder();
			var dynamicFlag1 = false;

			if (!string.IsNullOrEmpty(roleName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sr.display_name like concat('%', #role_name#, '%')");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(description)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sr.description like concat('%', #description#, '%')");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" and ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			sqlBuilder.AppendLine(@"group by sr.role_id, sr.display_name, sr.description
			order by seq, convert(sr.display_name using gbk)");

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {
				// Do query.
				var c = new RoleTable();
				session.ExecutePagedQuery(c, sql, System.Data.CommandType.Text, startRowIndex ?? 0, maximumRows ?? int.MaxValue,
					parameterRoleName,
					parameterDescription,
					parameterApplicationId);

				return c;
			}
		} // end of FindRoles.

		/// <summary>
		/// FindRolesCount
		/// </summary>
		/// <param name="applicationId" ></param>
		/// <param name="roleName" ></param>
		/// <param name="description" ></param>
		/// <returns></returns>
		public int FindRolesCount(
			int applicationId,
			string roleName = "",
			string description = "") {

			if (roleName != null) {
				roleName = roleName.Trim();
			}
			if (description != null) {
				description = description.Trim();
			}

			var parameterRoleName = new MyLib.DbParameter("role_name", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, roleName);
			var parameterDescription = new MyLib.DbParameter("description", MyLib.DbParameterType.String, MyLib.DbParameterMatchType.Contains, 0, System.Data.ParameterDirection.Input, description);
			var parameterApplicationId = new MyLib.DbParameter("application_id", MyLib.DbParameterType.Integer, applicationId);

			// Create sql.

			var sqlBuilder = new System.Text.StringBuilder();
			sqlBuilder.AppendLine(@"select count(*)	
from sec_role as sr
where application_id = #application_id#");

			// Create dynamic sql 1.

			var dynamicFlag1 = false;
			var dynamicText1 = new System.Text.StringBuilder();

			if (!string.IsNullOrEmpty(roleName)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sr.display_name like concat('%', #role_name#, '%')");

				dynamicFlag1 = true;
			}
			if (!string.IsNullOrEmpty(description)) {
				if (dynamicFlag1)
					dynamicText1.AppendLine(" and ");

				dynamicText1.AppendLine(@"sr.description like concat('%', #description#, '%')");

				dynamicFlag1 = true;
			}

			if (dynamicFlag1) {
				sqlBuilder.Append(" and ");
				sqlBuilder.Append(dynamicText1.ToString());
			}

			// Sql text.

			var sql = sqlBuilder.ToString();

			using (var session = SessionFactory.CreateReadOnlySession()) {
				// Do query.

				var c = session.ExecuteScalar<int>(sql, System.Data.CommandType.Text,
					parameterRoleName, parameterDescription, parameterApplicationId);

				return c;
			}
		} // end of FindRolesCount.

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
		public ActionTable FindActions() {
			var dt = new ActionTable();

			dt.BeginLoadData();

			ActionRow row;

			row = dt.AddNewRow();
			row.ActionId = 101;
			row.DisplayName = Properties.Main.ActionCreateUser;
			row.Description = Properties.Main.ActionCreateUser;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 102;
			row.DisplayName = Properties.Main.ActionDeleteUser;
			row.Description = Properties.Main.ActionDeleteUser;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 103;
			row.DisplayName = Properties.Main.ActionEditUser;
			row.Description = Properties.Main.ActionEditUser;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 104;
			row.DisplayName = Properties.Main.ActionQueryUser;
			row.Description = Properties.Main.ActionQueryUser;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 106;
			row.DisplayName = Properties.Main.ActionCreateRole;
			row.Description = Properties.Main.ActionCreateRole;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 107;
			row.DisplayName = Properties.Main.ActionDeleteRole;
			row.Description = Properties.Main.ActionDeleteRole;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 108;
			row.DisplayName = Properties.Main.ActionEditRole;
			row.Description = Properties.Main.ActionEditRole;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			row = dt.AddNewRow();
			row.ActionId = 109;
			row.DisplayName = Properties.Main.ActionQueryRole;
			row.Description = Properties.Main.ActionQueryRole;
			row.GroupName = Properties.Main.ActionUserManageGroup;

			dt.EndLoadData();

			dt.AcceptChanges();

			return dt;
		}

		/*
		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		[System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
		public ActionTable FindActions() {
			var sql = "select action_id actionId, display_name displayName, `description`, group_name `groupName` from sec_action order by group_name, `seq`";

			using (var session = SessionFactory.CreateReadOnlySession()) {
				// Do query.
				var c = new ActionTable();
				session.ExecuteQuery(c, sql, System.Data.CommandType.Text);

				return c;
			}
		} // end of FindActions.
		 */
	} // end of AuthorityProvider.
} // end of KJCG.DataAccess.
